package com.tangdi.Dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.common.DBConnect;
import com.tangdi.mkfile.gzdwtzs.CreateDWGZPDF_TZS;

public class GongziBankInfoImpl {
	private Logger logger = Logger.getLogger(CreateDWGZPDF_TZS.class);
	Connection con=DBConnect.getInstance().getConnection();
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	public String getGZ_COUNT(String month, String sql_condition){
		String  count_num = "";
		String sql = "SELECT COUNT(1) AS COUNT_NUM FROM CZGZ_ZGGZMX WHERE YF = " + month;
		sql = sql + sql_condition;
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				count_num = rs.getString(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
			return "";
		}finally{
			if(pstmt!=null)
				try{
					pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						}
			}
		return count_num;
	}
	
	public String getGZ_TOTAL(String month, String sql_condition){
		String  sf_txn_amt_total = "";
		String sql = "SELECT TO_NUMBER(SUM(SF_TXN_AMT)) AS TOTAL_AMT FROM CZGZ_ZGGZMX WHERE YF = " + month;
		sql = sql + sql_condition;
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				sf_txn_amt_total = rs.getString(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
			return "";
		}finally{
			if(pstmt!=null)
				try{
					pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						}
			}
		return sf_txn_amt_total;
	}
	
	public List<CZGZ_ZGGZMX> getGZ_ERROR(String month){
		List<CZGZ_ZGGZMX>  list_error = new ArrayList<CZGZ_ZGGZMX>();
		String sql = "SELECT TRIM(DW_MC) AS DW_MC, TRIM(NME) AS NME, TRIM(PER_AC) AS PER_AC, TRIM(SF_TXN_AMT) AS SF_TXN_AMT "
				   + " FROM CZGZ_ZGGZMX WHERE TXN_AMT != '0.00'  AND TXN_AMT != '' AND PER_AC != '2' AND DTL_STS != 'S' AND YF = " + month;
		//logger.info(sql);
		
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_ZGGZMX mx = new CZGZ_ZGGZMX();
				mx.setDW_MC(rs.getString("DW_MC"));
				mx.setNME(rs.getString("NME"));
				mx.setPER_AC(rs.getString("PER_AC"));
				mx.setSF_TXN_AMT(rs.getString("SF_TXN_AMT"));
				
				list_error.add(mx);
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			if(pstmt!=null)
				try{
					pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						}
			}
		return list_error;
	}
	
	public List<CZGZ_ZGGZMX> getGZ_DianHui(String month){
		List<CZGZ_ZGGZMX>  list_dh = new ArrayList<CZGZ_ZGGZMX>();
		String sql = "SELECT TRIM(DW_MC) AS DW_MC, TRIM(NME) AS NME, TRIM(SF_TXN_AMT) AS SF_TXN_AMT, TRIM(DHKH) AS DHKH, TRIM(DHDZ) AS DHDZ "
				   + " FROM CZGZ_ZGGZMX WHERE PER_AC = '2' AND YF = " + month;
		//logger.info(sql);
		
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_ZGGZMX mx = new CZGZ_ZGGZMX();
				mx.setDW_MC(rs.getString("DW_MC"));
				mx.setNME(rs.getString("NME"));
				mx.setSF_TXN_AMT(rs.getString("SF_TXN_AMT"));
				mx.setDHKH(rs.getString("DHKH"));
				mx.setDHDZ(rs.getString("DHDZ"));
				
				list_dh.add(mx);
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			if(pstmt!=null)
				try{
					pstmt.close();
					}catch(SQLException e){
						e.printStackTrace();
						}
			}
		return list_dh;
	}	
}
